/**
 * 
 * Copyright 2005 SIB Framework

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   
 */

package org.sibframework.sda;

import java.beans.PropertyDescriptor;
import java.io.PrintWriter;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.beanutils.PropertyUtils;
import org.sibframework.sda.manager.IModelManager;
import org.sibframework.sda.monitor.SdaMonitor;
import org.sibframework.sda.reflect.TableType2JavaType;
import org.sibframework.utils.Describer;
import org.sibframework.utils.Naming;
import org.sibframework.utils.ReflectTool;
import org.sibframework.utils.SqlUtility;
import org.sibframework.utils.TraceTool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 实现Simple Data Access思想的主类。
 * the main class to implement simple database access think.
 * @author Spring Song
 *
 */


public class SimpleDataAccess {
	private static SimpleDataAccess instance;
	

	/**
	 * 数据库连接超时时间10s
	 */
	public static final long connectionTimeout = 1000 * 10 * 1;
	
	/**
	 * java日期类型对应字符串的缺省格式。
	 */
	public static final String dateFormatStrJava = "yyyy-MM-dd";// HH:mm:ss";
	
	/**
	 * 数据库日期类型对应字符串的缺省格式
	 */
	public static final String dateFormatStrDb = "'yyyy-MM-dd'";// HH24:mm:ss'";
	

	private static SdaMonitor monitor = null;
	
	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
	
	private TraceTool tracetool = new TraceTool();
	
	
	public static SdaMonitor getMonitor() {
		return monitor;
	}
	
	
	public static void setMonitor(SdaMonitor monitor) {
		SimpleDataAccess.monitor = monitor;
	}

	/**
	 * 设置使用外部连接池的data source
	 * 如果设置该值代表使用连接池模式，否则使用无连接池模式。
	 */
	private static DataSource defaultDataSource = null;
	public static DataSource getDefaultDataSource() {
		return defaultDataSource;
	}
	public static void setDefaultDataSource(DataSource defaultDataSource) {
		SimpleDataAccess.defaultDataSource = defaultDataSource;
	}

	private static boolean useConnectionPool = false;
	
	
	public static boolean isUseConnectionPool() {
		return useConnectionPool;
	}
	
	public synchronized static void useConnectionPool(DataSource defaultDataSource) throws SQLException {
		//check data source.
		defaultDataSource.getConnection().close();
		SimpleDataAccess.useConnectionPool = true;
		setDefaultDataSource(defaultDataSource);
	}
	
	public synchronized static void waitDatabase(DataSource dataSource) throws SQLException {
		Connection conn = null;
		while(conn == null) {
			try {
				conn = dataSource.getConnection();
			} catch (Exception e) {
				System.err.println("Wait database up. " + e.getLocalizedMessage());
				try {
					Thread.sleep(1000);
				} catch (InterruptedException e1) {}
			}
		}
		conn.close();
	}
	

	/**
	 * 是否打印sql语句。仅在blank sql模式生效。
	 */
	private boolean printSql = false;
	private boolean debug = false;
	
	private boolean inTransaction = false;
	private Connection connection;
	private DataSource dataSource = null;
	private boolean closed = false;
	private long connectionStart = 0;
	private List<String> closedBy = null;

	public List<String> getClosedBy() {
		return closedBy;
	}


	/**
	 * get singleton instance
	 * 得到当前在使用的sda对象, 符合单态模式, 减少对象和链接的反复创建和关闭, 提高系统速度和效率.
	 * @return instance
	 * @throws Exception 
     * @deprecated 
     * replaced by <code>SdaFactory.getInstance()</code>.
	 */
	@Deprecated
	private synchronized static SimpleDataAccess getInstance() throws SQLException {
		if (instance == null || instance.isClosed()) {
			instance = new SimpleDataAccess();
		} else if (instance.connection != null) {
			instance.checkConnection();
		} //TODO remove
		return instance;
	}
	
	
	/**
	 * start a new SDA 。 开始一个新sda， 注意：一定要及时关闭。
	 * 
	 * @return save point object to roll back;
	 * @throws Exception
	 */
	private synchronized static SimpleDataAccess createInstance() throws SQLException {
		return new SimpleDataAccess();
	}
	
	/**
	 * get current used connection
	 * 得到当前sda的connection链接对象.
	 * @return Connection object current used.
	 * @throws SQLException 
	 */
	public synchronized Connection getConnection() throws SQLException {
		//long start = System.currentTimeMillis();
		if (this.isClosed()) {
			logger.error("★ Sda Warning: Use a Closed SDA #" + this.hashCode() + ". ");;
			Describer.describe(this.tracetool.trace(), new PrintWriter(System.err), "");
			System.out.println("---#" + this.hashCode() + " was closed by ");
			Describer.describe(this.closedBy);
			this.closed = false;
			//throw new ClosedSdaException();
		}
		
		this.checkConnection();
		

		if (connection == null) {
			connection = dataSource.getConnection();
			//System.err.println(System.currentTimeMillis() + "^^^Get Connection: " + connection.hashCode());		
			//System.err.println("create connection : " + connection.hashCode());
			if (monitor != null) {
				monitor.addRequestConnection();
			}
			this.connectionStart = System.currentTimeMillis();
		}
		
		//start = System.currentTimeMillis() - start;
		//System.err.println("Check connection use:" + start);

		return connection;
	}
	
	/**
	 * 检查连接是否超时，是否关闭。
	 * @throws SQLException
	 */
	public synchronized void checkConnection() throws SQLException {
		
		if (connection != null) {
			if (connection.isClosed()) {
				connection = null;
			} else if (System.currentTimeMillis() - this.connectionStart > connectionTimeout) {
				if (!connection.isValid(1)) {
					if (this.debug) {
						//log timeout;
						System.err.println("Connection Timeout: " + connection.hashCode());
					}
					this.closeConnection();
				} else {
					this.connectionStart = System.currentTimeMillis();
				}
			}
		}
	
	}


	
	private synchronized void closeConnection() throws SQLException {
		if (this.connection != null) {
			Connection conn = this.connection;
			this.connection = null;
			
			//如果已经关闭，则不再重复关闭，也不会增加关闭记录。
			if (conn.isClosed()) {
				return;
			}
			
			try {
				//System.err.println(System.currentTimeMillis() + "^^^Closing Connection: " + conn.hashCode());
				//System.err.println("");
				//System.err.println(System.currentTimeMillis() + "^^^Closed Connection: " + conn.hashCode());	
				if (monitor != null) {
					monitor.addReturnConnection();
				}
				conn.close();
			} catch (Exception e) {
				System.err.println(e.getLocalizedMessage());
			}
		}
	}
	
	
	public static void setInstance(SimpleDataAccess instance) {
		SimpleDataAccess.instance = instance;
	}
	/**
	 * 查询当前是否打印sql, 仅对blank sql模式有效. 对于预编译类型的操作,使用连接池的debug模式来打印sql
	 * get the log status
	 * @return true if print SQL.
	 */
	public boolean isPrintSql() {
		return printSql;
	}

	/**
	 * 设置当前是否打印sql, 仅对blank sql模式有效.. 对于预编译类型的操作,使用连接池的debug模式来打印sql
	 * set the log status
	 * @param printSql true if allow print SQL
	 */
	public void setPrintSql(boolean printSql) {
		this.printSql = printSql;
	}

	public boolean isDebug() {
		return debug;
	}
	public void setDebug(boolean debug) {
		this.debug = debug;
	}
	/**
	 * instance simple data access object use default connection factory.
	 * @throws Exception 
	 * 
	 */
	public SimpleDataAccess() throws SQLException{
		if (defaultDataSource != null) {
			dataSource = defaultDataSource;
		} else {
			dataSource = new ConnectionFactory();
		}
		if (instance != null) {
			this.setDebug(instance.isDebug());
		}

		if (monitor != null) {
			monitor.addCreateSDA(this.tracetool.locate(this));
		}
	}
	


	/**
	 * instance simple data access object use a given datasource.
	 * 根据传入的Datasource对象来创建sda对象,并以静态变量的方式把该对象注册为本类的单态实例.
	 * @param ds javax.sql.DataSource implement.
	 * @throws SQLException
	 */
	public SimpleDataAccess(DataSource ds) throws SQLException{
		this.dataSource = ds;
		
		if (instance != null) {
			this.setDebug(instance.isDebug());
		}
		
		if (monitor != null) {
			monitor.addCreateSDA(this.tracetool.locate(this));
		}
		
		getConnection();
		
	}
	

	/**
	 * close this instance
	 * 关闭自身.
	 */
	public synchronized void close() {
		
		//如果是已经关闭的sda，打印错误信息，并直接退出，不会二次关闭，也不会重复记录关闭信息。
		if (isClosed()) {
			
			System.err.println("★ Sda Warning:Close a closed sda #" + hashCode() + ". Refused. ");
			Describer.describe(this.tracetool.trace(), new PrintWriter(System.err), "");
			System.out.println("---#" + this.hashCode() + " was closed by ");
			Describer.describe(this.closedBy);
			
			//instance = null;
			return;
		}
		
		//finish transaction.
		if (this.inTransaction) {
			try {
				this.finishTransaction();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		//check public instance
		if (instance == this) {
			System.err.println("★ Sda Warning:Close a public sda. Refused: ");
			Describer.describe(this.tracetool.trace(), new PrintWriter(System.err), "");
			//instance = null;
			return;
		}

		//check public instance
		if (SdaFactory.instance == this) {
			System.err.println("★ Sda Warning:Close a public sda (in SdaFactory). Refused: ");
			Describer.describe(this.tracetool.trace(), new PrintWriter(System.err), "");
			//SdaFactory.instance = null;
			return;
		}

		if (monitor != null) {
			monitor.addCloseSDA(this.tracetool.locate(this));
		}

		this.closed = true;
		this.inTransaction = false;
		try {
			this.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.closedBy = this.tracetool.trace();
	}
	
	/**
	 * start a new SDA for a transaction。 开始一个新的事物， 注意：一定要及时关闭。
	 * 
	 * @return save point object to roll back;
	 * @throws Exception
	 * 
	 */
	public synchronized SimpleDataAccess createTransactionInstance() throws SQLException {

		SimpleDataAccess transda = new SimpleDataAccess(this.dataSource);
		transda.inTransaction = true;
		transda.debug = this.debug;
		transda.getConnection().setAutoCommit(false);
		
/*			if (this.isDebug()) {
				System.err.println("#########-----Start Transaction-----------###########");
			}*/
		
		if (monitor != null) {
			monitor.addStartedTrans(this.tracetool.locate(this));
		}
		return transda;

		
	}
	
	/**
	 * start a new SDA for a transaction, and make the bean to use trancation sda.
	 * 注意：一定要及时关闭。关闭的时候还要在注入一次manager的sda
	 * @param bean bean to init model manager with trancation sda. 用来做事物处理的action类或者其他算法类，用来对其中所有的model manager注入事物的sda。
	 * @return save point object to roll back;
	 * @throws Exception
	 */
	public synchronized SimpleDataAccess createTransactionInstance(Object bean) throws SQLException {
		SimpleDataAccess transda = this.createTransactionInstance();
		transda.injectManager(bean, transda);
		return transda;
	}

	/**
	 * @deprecated use cancelTransaction()
	 * 回退事物，并关闭食物。
	 * @throws SQLException
	 */
	public void rollbackTransaction() throws SQLException {
		this.cancelTransaction();
	}
	
	/**
	 * roll back a transaction, and finish that transaction
	 * 回退事物，并关闭食物。
	 * @throws SQLException
	 */
	public synchronized void cancelTransaction() throws SQLException {
		
		
		if (this.connection == null || this.connection.getAutoCommit()) {
			return;
		}
		
		try {
			this.connection.rollback();
			if (monitor != null) {
				monitor.addRollback();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		this.closeTransaction();
	}
	
	
	public void cancelTransaction(Object bean) throws SQLException {

		this.cancelTransaction();
				
		if (bean != null) {
			this.injectManager(bean, null);
		}
	}

	/**
	 * @deprecated use finishTransaction();
	 * commit a transaction, keep transaction mode.
	 * 提交事务，但仍保持sda的事物处理模式。要彻底关闭事物，必须显式的调用finishTransaction
	 * @throws SQLException
	 */
	public void commitTransaction() throws SQLException {
		this.finishTransaction();
	}
	
	
	/**
	 * commit and finish a transaction. and take care the bean's sda.
	 * 提交和完成一个事物，关闭事物模式。关闭当前的事物的链接。并将传入的类中所有的model manager对象的sda替换为非事物的.
	 * @param bean bean to init model manager with trancation sda.
	 * @throws SQLException
	 */
	public void finishTransaction(Object bean) throws SQLException {
		this.finishTransaction();
		this.injectManager(bean, null);
	}
	
	
	
	/**
	 * commit and finish a transaction. close transaction model. close sda.
	 * 提交和完成一个事物，关闭事物模式。关闭当前的事物的链接。
	 * @throws SQLException
	 */
	public synchronized void finishTransaction() throws SQLException {
		
		
		if (SimpleDataAccess.instance == this) {
			SimpleDataAccess.instance = null;
		}
		
		//无需提交和关闭事物
		if (connection == null || connection.getAutoCommit()) {
			System.err.println("Finish a not transaction.");
			return;
		}
		
		//提交
		try {
			connection.commit();
			if (monitor != null) {
				monitor.addCommit();
			}
		} catch (Exception e) {
			System.err.println(e.getLocalizedMessage());
		}
			
		this.closeTransaction();
		
		
	}
	
	/**
	 * close a transaction. close transaction. close sda.
	 * 关闭一个事物，关闭事物模式。关闭当前的事物的链接。
	 * @throws SQLException
	 */
	public synchronized void closeTransaction() throws SQLException {
		
		//判断无需关闭的事物
		if (connection == null || connection.getAutoCommit()) {
			System.err.println("Close a not transaction." + new TraceTool().trace());
			return;
		}
		
		//结束事物
		try {
				
			//关闭事物模式
			connection.setAutoCommit(true);

			//监控
			if (monitor != null) {
				monitor.addFinishedTrans(this.tracetool.locate(this));
			}
			
		} catch (Exception e) {
			System.err.println(e.getLocalizedMessage());
		}
	
		
		//记录事物标记
		if (!this.inTransaction) {
			if (SimpleDataAccess.instance == this) {
				try {
					throw new SQLWarning("Try finish no transaction sda. 非法操作，尝试提交并关闭不在事物中的sda。\n这仅仅是一个警告信息，提醒你使用正确的事物API。");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		this.inTransaction = false;

		//debug
/*		if (this.isDebug()) {
			System.err.println("#########-----Finish Transaction-----------###########");
		}
*/
		
		this.close();
	}

	
	/**
	 * 为传入的bean以及bean内的manager注入sda。
	 * @param bean
	 * @param sda
	 */
	public void injectManager(Object bean, SimpleDataAccess sda) {
		try {
			if (bean instanceof IModelManager) {
				((IModelManager) bean).setSDA(sda);
			}
			
			for (PropertyDescriptor pd : PropertyUtils.getPropertyDescriptors(bean)) {
				if (IModelManager.class.isAssignableFrom(pd.getPropertyType())) {
					try {
						IModelManager mm = (IModelManager) PropertyUtils.getProperty(bean, pd.getName());
						mm.setSDA(sda);
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						e.printStackTrace();
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
					}
					
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	/**
	 * list all data of a Class, that will query all data in a table.
	 * default without sort by primary key.
	 * 列出一个表的所有数据.不排序
	 * @param objectClass 要列出所有对象的类
	 * @return all data of a Class
	 * @throws SQLException 
	 */

	public<T> List<T> listAll(Class<T> objectClass) {
		return this.listAll(objectClass, true);
	}
	
	/**
	 * list all data of a Class, that will query all data in a table.
	 * 列出一个表的所有数据,并按照指定进行排序或者不排序
	 * @param objectClass Class of the object.  要列出所有对象的类
	 * @param isSort if true, sort by primary key. 是否按照主键排序
	 * @return all data of a Class
	 * @throws SQLException 
	 */
	public<T> List<T> listAll(Class<T> objectClass, boolean isSort) {
		TableProvider tp = TableRegister.getTableProvider(objectClass);
		if (tp == null) {
			return Collections.emptyList();
		}
		String sql = "select * from " + tp.getTableName();
		try {
			Method pkget = objectClass.getMethod(SqlUtility.column2PropertyGetter(tp.getPrimaryKey()), new Class[]{});
			if (isSort) {
				if (pkget.getReturnType() == Integer.class || pkget.getReturnType() == Long.class) {
					sql += " order by ceil(" + tp.getPrimaryKey() + ")"; 
				} else {
					sql += " order by " + tp.getPrimaryKey(); 
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.query(sql, objectClass);
	}
	
	/**
	 * find a object by primary key.
	 * 根据主键查找一个对象
	 * @param object object with primary key seted, 要查找的对象, 该对象应该含有一个主键的值, 用来查找.
	 * @return object
	 */
	public<T> T findByPK(T object) {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return null;
		}
		try {
			String sql = "select * from " + tp.getTableName() 
				+ " where " + tp.getPrimaryKey() + " = ";
			Method method = object.getClass().getMethod(SqlUtility.column2PropertyGetter(tp.getPrimaryKey()), new Class[0]);
			if (method.getReturnType() == String.class) {
				sql += "'";
			}
			sql += method.invoke(object, new Object[0]);
			if (method.getReturnType() == String.class) {
				sql += "'";
			}
			@SuppressWarnings("unchecked")
			Class<T> clazz = (Class<T>) object.getClass();
			T result = this.queryFirst(sql, clazz);
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}


	/**
	 * find a object by primary key. 
	 * 根据主键查找一个对象.
	 * @param clazz class of the object. to help know which table you are search for.
	 * @param ser pk
	 * @return object
	 * @throws SQLException 
	 */
	public<T> T findByPK(Class<T> clazz, Serializable ser) {
		TableProvider tp = TableRegister.getTableProvider(clazz);
		if (tp == null) {
			return null;
		}
		String sql = "select * from " + tp.getTableName() 
			+ " where " + tp.getPrimaryKey() + " = ";
		if (ser instanceof String) {
			sql += "'";
		}
		sql += ser;
		if (ser instanceof String) {
			sql += "'";
		}
		T result = this.queryFirst(sql, clazz);
		return result;
		
		
	}
	
	/**
	 * find a object by a given code property and code value.
	 * 根据指定的code属性名和code值查找一个对象.
	 * @param clazz object class to get，要查找的对象
	 * @param codeName code name, SQL format like "user_code"。用来查找的code的名字
	 * @param ser code value.查找的code的值。
	 * @return the first Object match the code name and value.
	 */
	public<T> T getByCode(Class<T> clazz, String codeName, Serializable ser) {
		TableProvider tp = TableRegister.getTableProvider(clazz);
		if (tp == null) {
			return null;
		}
		try {
			String sql = "select * from " + tp.getTableName() 
				+ " where " + codeName + " = ";
			if (ser instanceof String) {
				sql += "'";
			}
			sql += ser;
			if (ser instanceof String) {
				sql += "'";
			}
			T result = this.queryFirst(sql, clazz);
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	

	/**
	 * search a objects by a given code property and code value.
	 * 根据指定的code属性名和code值查找一个对象.
	 * @param clazz object class to get，要查找的对象
	 * @param codeName code name, SQL format like "user_code"。用来查找的code的名字
	 * @param ser code value.查找的code的值。
	 * @return the first Object match the code name and value.
	 */
	public<T> List<T> searchByCode(Class<T> clazz, String codeName, Serializable ser) {
		TableProvider tp = TableRegister.getTableProvider(clazz);
		if (tp == null) {
			return null;
		}
		try {
			String sql = "select * from " + tp.getTableName() 
				+ " where " + codeName + " = ";
			if (ser instanceof String) {
				sql += "'";
			}
			sql += ser;
			if (ser instanceof String) {
				sql += "'";
			}
			List<T> result = this.query(sql, clazz);
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	/**
	 * search object from database by any give field.
	 * Field is null will not be use.
	 * Field set with value will be used to search.  
	 * 搜索一个对象，根据对象内非空的值来查找类似的对象。
	 * @param object with some field set. 用来 搜索的对象，对象的所有的非空的值都会用来做为过滤的条件。
	 * @return object list of the result.
	 * @throws SQLException 
	 */
	public<T> List<T> search(T object) {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return Collections.emptyList();
		}
		StringBuffer sql = new StringBuffer("select * from ").append(tp.getTableName());
		sql.append(" where 1=1 ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (o != null) {
					sql.append(" and ");
					
					if (o instanceof Date) {
						sql.append("to_char(").append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", " + dateFormatStrDb+ ")");						
					} else {
						sql.append(SqlUtility.propertyGetterIser2Column(getter.getName()));
					}
					
					sql.append(" = ");
					if (o instanceof String || o instanceof Date) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat(dateFormatStrJava).format((Date) o));
					} else {
						sql.append(o.toString());
					}
					if (o instanceof String || o instanceof Date) {
						sql.append("'");
					}
				}
			}
		}
		try {

			if (tp.getPrimaryKey() != null) {
				sql.append(" order by " + tp.getPrimaryKey()); 
			}
		} catch (Exception e) {
			System.err.println(e.getLocalizedMessage());
		}
		//tp.getPrimaryKey() + " = '" + ReflectTool.getInstance().getPreperty(simpleObject, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) object.getClass();
		return this.query(sql.toString(), clazz);
	}

	
	/**
	 * search object from database by any give field.
	 * Field is null will not be use.
	 * Field set with value will be used to search.  
	 * 搜索一个对象，根据第一个参数对象内非空的值来查找类似的对象，并根据第二个参数对象内的非空值来模糊过滤。
	 * @param object with some field set. 用来 搜索的对象，对象的所有的非空的值都会用来做为过滤的条件。
	 * @param like ，用来 模糊搜索的条件的对象，对象的所有的非空的值都会用来做为模糊过滤的条件。
	 * @return object list of the result.
	 * @throws SQLException 
	 */
	public<T> List<T> search(T object, T like) {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return Collections.emptyList();
		}
		StringBuffer sql = new StringBuffer("select * from ").append(tp.getTableName());
		sql.append(" where 1=1 ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (o != null) {
					sql.append(" and ");
					
					if (o instanceof Date) {
						sql.append("to_char(").append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", " + dateFormatStrDb+ ")");						
					} else {
						sql.append(SqlUtility.propertyGetterIser2Column(getter.getName()));
					}
					
					sql.append(" = ");
					if (o instanceof String || o instanceof Date) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat(dateFormatStrJava).format((Date) o));
					} else {
						sql.append(o.toString());
					}
					if (o instanceof String || o instanceof Date) {
						sql.append("'");
					}
					
					continue;
				} 
				try {
					o = getter.invoke(like, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (o != null) {
					sql.append(" and ");
					
					
					if (o instanceof Date) {
						sql.append("to_char(").append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", " + dateFormatStrDb+ ")");						
					} else {
						sql.append(SqlUtility.propertyGetterIser2Column(getter.getName()));
					}
					
					sql.append(" like '%");
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o) );
					} else {
						sql.append(o.toString());
					}
					sql.append("%'");
				}
			}
		}
		try {
			if (tp.getPrimaryKey() != null) {
				sql.append(" order by " + tp.getPrimaryKey()); 
			}
		} catch (Exception e) {
			System.err.println(e.getLocalizedMessage());
		}
		//tp.getPrimaryKey() + " = '" + ReflectTool.getInstance().getPreperty(simpleObject, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) object.getClass();
		return this.query(sql.toString(), clazz);
	}
	
	/**
	 * Same as search(Object object) but only return the first object. 
	 * 搜索对象并返回结果内的第一个对象，根据对象内非空的值来查找类似的对象。
	 * 
	 * @param object
	 * @return first object of the result.
	 * @throws SQLException 
	 */
	public<T> T searchFirst(T object)  {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return null;
		}
		StringBuffer sql = new StringBuffer("select * from ").append(tp.getTableName());
		sql.append(" where 1=1 ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (o != null) {
					sql.append(" and ");
					
					//to_char(sysdate, 'yyyy-MM-dd HH24:mm:ss')
					if (o instanceof Date) {
						sql.append("to_char(").append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", " + dateFormatStrDb+ ")");						
					} else {
						sql.append(SqlUtility.propertyGetterIser2Column(getter.getName()));
					}
					
					
					sql.append(" = ");
					if (o instanceof String || o instanceof Date) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat(dateFormatStrJava).format((Date) o));
					} else {
						sql.append(o.toString());
					}
					if (o instanceof String || o instanceof Date) {
						sql.append("'");
					}
				}
			}
		}
		try {
			if (tp.getPrimaryKey() != null) {
				sql.append(" order by " + tp.getPrimaryKey()); 
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		//tp.getPrimaryKey() + " = '" + ReflectTool.getInstance().getPreperty(simpleObject, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) object.getClass();
		return this.queryFirst(sql.toString(), clazz);
	}

	/**
	 * @deprecated please use update2 replaced. 不赞成继续使用的，请使用update2替代。
	 * save a object change to database. use blank sql string.使用文本sql语句，对于注入攻击是不安全的。
	 * @param object
	 * @return count of rows affored.
	 * @throws SQLException 
	 */
	public int update(Object object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("update ").append(tp.getTableName()).append(" set ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				String cn = SqlUtility.propertyGetterIser2Column(getter.getName());
				if (tp.getPrimaryKey().equalsIgnoreCase(cn)) {
					continue;
				}
				sql.append(cn);
				sql.append(" = ");
				if (o == null) {
					sql.append(" null ").append(", ");
				} else {
					if (o instanceof String || o instanceof Date || o instanceof Boolean) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o));
					} else {
						sql.append(prepareString(o));
					}
					if (o instanceof String || o instanceof Date || o instanceof Boolean) {
						sql.append("'");
					}
					sql.append(", ");
				}
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		try {
			@SuppressWarnings("rawtypes")
			Class retype = object.getClass().getMethod(pkgetstr, new Class[0]).getReturnType();
			if (retype == String.class) {
				sql.append(" where ").append(tp.getPrimaryKey()).append(" = '");
				sql.append(ReflectTool.getInstance().getPreperty(object, pkgetstr));
				sql.append("'");
			} else {
				sql.append(" where ").append(tp.getPrimaryKey()).append(" = ");
				sql.append(ReflectTool.getInstance().getPreperty(object, pkgetstr));
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return this.write(sql.toString());
	}
	
	/**
	 * Save a object change to database. use Prepared Statement "?" to set parameter. inject safe.
	 *  使用预编译的sql由于据，对于注入攻击是安全的，
	 * @param object 要插入的对象
	 * @return count of rows affored.
	 * @throws SQLException 
	 */
	public<T> int update2(T object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("update ").append(tp.getTableName()).append(" set ");
		Method[] getters = object.getClass().getDeclaredMethods();
		int index = 1;
		Map<Integer, Object> valueMap = new HashMap<Integer, Object>();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				String cn = SqlUtility.propertyGetterIser2Column(getter.getName());
				if (tp.getPrimaryKey().equalsIgnoreCase(cn)) {
					continue;
				}
				sql.append(cn);
				sql.append(" = ?, ");

				valueMap.put(index, o);
				index++;
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		sql.append(" where ").append(tp.getPrimaryKey()).append(" = ?");

		valueMap.put(index, ReflectTool.getInstance().getPreperty(object, pkgetstr));
		index++;

		PreparedStatement pstm = null;
		int count = 0;
		try {
			pstm = getConnection().prepareStatement(sql.toString());
			for (int i = 1; i < index; i++) {
				this.setParameter(pstm, i, valueMap.get(i));
			}
			count = pstm.executeUpdate();
			pstm.close();
		} catch (SQLException e) {
			if(pstm != null) {
				pstm.close();
			}
			throw e;
		}
		
		return count;
		
	}
	
	/**
	 * save a list of objects change to database. 更新一组对象到数据库。这一组对象必须是同一个类型。
	 * @param objects Object Array to update.
	 * @return count of rows affored.
	 * @throws SQLException 
	 */
	public<T> int update2(T[] objects) throws SQLException{
		
		if (objects == null && objects.length == 0) {
			return 0;
		}
		
		
		T object = objects[0];
		TableProvider tp = TableRegister.getTableProvider(objects[0].getClass());
		if (tp == null) {
			return 0;
		}
		
		//update head
		StringBuffer sql = new StringBuffer("update ").append(tp.getTableName()).append(" set ");
		
		//get all properties
		PropertyDescriptor[] properties = PropertyUtils.getPropertyDescriptors(object);

		//parameter set
		Map<Integer, PropertyDescriptor> valueMap = new HashMap<Integer, PropertyDescriptor>();
		
		//primary key property
		PropertyDescriptor pkProperty = null;
		int index = 0;
		for (int i = 0; i < properties.length; i++) {
			PropertyDescriptor property = properties[i];
			
			//get db column name
			String cn = SqlUtility.property2Column(property.getName());
			
			//skip primary key
			if (tp.getPrimaryKey().equalsIgnoreCase(cn)) {
				pkProperty = property;
				continue;
			} if (property.getName().equalsIgnoreCase("class")) {
				continue;
			}
			
			//append to sql string
			sql.append(cn);
			sql.append(" = ?, ");
			
			//save property index.
			index++;
			valueMap.put(index, property);
		}
		
		//delete last ","
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		
		
		//get primary key property.
		if (pkProperty == null) {
			try {
				pkProperty = PropertyUtils.getPropertyDescriptor(object, SqlUtility.column2Property(tp.getPrimaryKey()));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		//where section
		sql.append(" where ").append(tp.getPrimaryKey()).append(" = ?");
		index++;
		valueMap.put(index, pkProperty);

		//prepare statement
		PreparedStatement pstm = null;
		int count = 0;
		pstm = getConnection().prepareStatement(sql.toString());
		
		try {
			//update each object
			for (T o : objects) {
				
				//set parameter as saved parameter index.
				for (int i = 1; i <= index; i++) {
					
					PropertyDescriptor property = valueMap.get(i);
					
					Object value = null;
					try {
						value = PropertyUtils.getProperty(o, property.getName());
					} catch (Exception e) {
						e.printStackTrace();
					}
					
					this.setParameter(pstm, i, value);

				}
				count = pstm.executeUpdate();
				pstm.close();
			}
		} catch (SQLException e) {
			if(pstm != null) {
				pstm.close();
			}
			throw e;
		}
		
		
		return count;
		
	}

	
	/**
	 * 根据Map的值来更新一个对象到数据库。
	 * @param clazz class to update.
	 * @param id id of record to update.
	 * @param values values to update.
	 * @return count of rows afford.
	 * @throws SQLException
	 */
	public int update2(Class clazz, Serializable id, Map<String, Object> values) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(clazz);
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("update ").append(tp.getTableName()).append(" set ");
		Method[] getters = clazz.getDeclaredMethods();
		int index = 1;
		Map<Integer, Object> valueMap = new HashMap<Integer, Object>();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				String cn = SqlUtility.propertyGetterIser2Column(getter.getName());
				if (tp.getPrimaryKey().equalsIgnoreCase(cn)) {
					continue;
				}
				Naming naming = Naming.createBySqlName(cn);
				o = values.get(naming.getFieldName());
				if (o == null) {
					continue;
				}
				sql.append(cn);
				sql.append(" = ?, ");

				valueMap.put(index, o);
				index++;
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		sql.append(" where ").append(tp.getPrimaryKey()).append(" = ?");

		valueMap.put(index, id);
		index++;

/*		if (this.isPrintSql()) {
			System.out.println(sql.toString());
			Describer.describe(valueMap);
		}*/
		PreparedStatement pstm = null;
		int count = 0;

		try {
			pstm = getConnection().prepareStatement(sql.toString());
			for (int i = 1; i < index; i++) {
				this.setParameter(pstm, i, valueMap.get(i));
			}
			count = pstm.executeUpdate();
			pstm.close();
		} catch (SQLException e) {
			if(pstm != null) {
				pstm.close();
			}
			throw e;
		}
		
		return count;
		
	}
	/**
	 * delete a object from database. 
	 * 删除一个对象，根据对象的主键的值来删除数据库中对应的记录.
	 * @param object the object to delete
	 * @return the count has deleted.
	 * @throws SQLException 
	 * @throws NoSuchMethodException 
	 * @throws SecurityException 
	 */
	public<T> int delete(T object) throws SQLException {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}

		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		String sql;
		
		@SuppressWarnings("rawtypes")
		Class retype = null;
		try {
			retype = object.getClass().getMethod(pkgetstr, new Class[0]).getReturnType();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getLocalizedMessage());
		}
		sql = "delete from " + tp.getTableName();
		sql += " where " + tp.getPrimaryKey() + " = "; 
		if (retype == String.class) {
			sql += "'" + ReflectTool.getInstance().getPreperty(object, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		} else {
			sql += ReflectTool.getInstance().getPreperty(object, SqlUtility.column2PropertyGetter(tp.getPrimaryKey()));
		}
		
		return this.write(sql);
	}
	
	
	/**
	 * delete a list of objects from database. 
	 * 删除一组对象，根据对象的主键的值来删除数据库中对应的记录.
	 * @param objects  objects to delete
	 * @return the count has deleted.
	 * @throws SQLException 
	 * @throws NoSuchMethodException 
	 * @throws SecurityException 
	 */
	public<T> int delete(T[] objects) throws SQLException {
		if(objects.length == 0) {
			return 0;
		}
		
		TableProvider tp = TableRegister.getTableProvider(objects[0].getClass());
		if (tp == null) {
			return 0;
		}

		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		String sql;
		

		sql = "delete from " + tp.getTableName();
		sql += " where " + tp.getPrimaryKey() + " = ?";
		
		//prepare statement
		PreparedStatement pstm = null;
		int count = 0;
		
		
		try {
			pstm = getConnection().prepareStatement(sql);
		
			//delete each object
			for (T o : objects) {
				
				PropertyDescriptor property = PropertyUtils.getPropertyDescriptor(o, tp.getPrimaryKey());
			
				Object value = null;
				value = PropertyUtils.getProperty(o, property.getName());
				if (value == null) {
					continue;
				}
				
				this.setParameter(pstm, 1, value);
				count += pstm.executeUpdate();
				

			
			}

			pstm.close();
		} catch(Exception e) {
			if(pstm != null) {
				pstm.close();
			}
			e.printStackTrace();
		}
		
		return count;
		
	}
	/**
	 * delete a object by primary key.
	 * 删除一个对象，根据传入的主键的值，来删除给定的class对应的库表中的记录。
	 * @param clazz object class
	 * @param ser primary key 主键值
	 * @return count of record deleted in database.
	 * @throws SQLException
	 */
	public int deleteByPK(Class clazz, Serializable ser) throws SQLException {
		TableProvider tp = TableRegister.getTableProvider(clazz);
		if (tp == null) {
			return 0;
		}
		
		String sql = "delete from " + tp.getTableName()
			+ " where " + tp.getPrimaryKey() + " = ";
		if (ser instanceof String) {
			sql += "'";
		}
		sql += ser;
		if (ser instanceof String) {
			sql += "'";
		}
		return this.write(sql);
			
	}
	
	
	
	/**
	 * @deprecated use insert2 replaced, 不建议使用，使用insert2取代。
	 * insert a object to database. 
	 * 插入一个对象到数据库
	 * @param object the object to insert.
	 * @return the number has inserted.
	 * @throws SQLException 
	 */
	public int insert(Object object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("insert into ").append(tp.getTableName()).append(" ( ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				//TODO there should has a dicision to skip column when getter return null;
				sql.append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", ");
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		sql.append(") values (");
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				//TODO there should has a dicision to skip column when getter return null;
				if (o == null) {
					sql.append(" null ").append(", ");
				} else {
					if (o instanceof String || o instanceof Date || o instanceof Boolean) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o));
					} else {
						sql.append(prepareString(o));
					}
					if (o instanceof String || o instanceof Date|| o instanceof Boolean) {
						sql.append("'");
					}
					sql.append(", ");
				}
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		sql.append(")");
		return this.write(sql.toString());
	}

	
	
	/**
	 * insert a object to database.
	 * 插入一个对象到数据库。主键不能为空。主键可以使用getMaxPk（Class）的方法来获得当前数据库最大的id，并＋１作为新的主键。
	 * @param object the object to insert.
	 * @return the number has inserted.
	 * @throws SQLException 
	 */
	public<T> int insert2(T object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("insert into ").append(tp.getTableName()).append(" ( ");
		Method[] getters = object.getClass().getDeclaredMethods();
		
		StringBuffer values = new StringBuffer();
		int index = 1;
		Map<Integer, Object> valueMap = new HashMap<Integer, Object>();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				
				sql.append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", ");
				values.append("?, ");
				try {
					Object o = getter.invoke(object, new Object[]{});
					valueMap.put(index, o);
					index++;
				} catch (Exception e) {
					e.printStackTrace();
				}
				
			}
		}
		
		
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		if (values.toString().endsWith(", ")) {
			values.delete(values.length() - 2, values.length());
		}
		sql.append(") values (").append(values).append(")");
		

		PreparedStatement pstm = null;
		int count = 0;
		try {
			pstm = getConnection().prepareStatement(sql.toString());
			for (int i = 1; i < index; i++) {
				if (valueMap.get(i) != null && valueMap.get(i) instanceof Date) {
					java.sql.Timestamp time = new Timestamp(((Date) valueMap.get(i)).getTime());
					pstm.setObject(i, time);
				} else {
					pstm.setObject(i, valueMap.get(i));
				}
			}		
			count = pstm.executeUpdate();
			pstm.close();
		} catch (SQLException e) {
			if(pstm != null) {
				pstm.close();
			}
			throw e;
		}
		return count;
		
	}
	
	
	
	/**
	 * insert a list of objects change to database. <p>
	 * 插入一组对象到数据库。这一组对象必须是同一个类型。
	 * 主键不能为空。主键可以使用getMaxPk（Class）的方法来获得当前数据库最大的id，并＋１作为新的主键。
	 * @param objects Object Array to insert.
	 * @return count of rows affored.
	 * @throws SQLException 
	 */
	public<T> int insert2(T[] objects) throws SQLException{
		
		if (objects == null && objects.length == 0) {
			return 0;
		}
		
		
		T object = objects[0];
		TableProvider tp = TableRegister.getTableProvider(objects[0].getClass());
		if (tp == null) {
			return 0;
		}
		
		//update head
		StringBuffer sql = new StringBuffer("insert into ").append(tp.getTableName()).append(" (");
		StringBuffer values = new StringBuffer();
		
		//get all properties
		PropertyDescriptor[] properties = PropertyUtils.getPropertyDescriptors(object);

		//parameter set
		Map<Integer, PropertyDescriptor> valueMap = new HashMap<Integer, PropertyDescriptor>();
		
		//primary key property
		PropertyDescriptor pkProperty = null;
		int index = 0;
		for (int i = 0; i < properties.length; i++) {
			PropertyDescriptor property = properties[i];
			
			//get db column name
			String cn = SqlUtility.property2Column(property.getName());
			
			//skip class
			if (property.getName().equalsIgnoreCase("class")) {
				continue;
			}
			
			//append to sql string
			sql.append(cn).append(", ");
			values.append("?, ");
			
			//save property index.
			index++;
			valueMap.put(index, property);
		}
		
		//delete last ","
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		
		if (values.toString().endsWith(", ")) {
			values.delete(values.length() - 2, values.length());
		}

		sql.append(") values (").append(values).append(")");
		
		

		//prepare statement
		PreparedStatement pstm = null;
		int count = 0;
		pstm = getConnection().prepareStatement(sql.toString());
		
		//update each object
		try {
			pstm = getConnection().prepareStatement(sql.toString());
			for (T o : objects) {
				
				//set parameter as saved parameter index.
				for (int i = 1; i <= index; i++) {
					
					PropertyDescriptor property = valueMap.get(i);
					
					Object value = null;
					try {
						value = PropertyUtils.getProperty(o, property.getName());
					} catch (Exception e) {
						e.printStackTrace();
					}
					
					this.setParameter(pstm, i, value);

				}
				count += pstm.executeUpdate();
			}
			pstm.close();
		} catch (SQLException e) {
			if(pstm != null) {
				pstm.close();
			}
			throw e;
		}
		return count;
		
	}
	
	
	
	/**
	 * set the parameter for prepared statement as it's own type.
	 * @param pstm
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	private void setParameter(PreparedStatement pstm, int index, Object value) throws SQLException {
		if (value == null) {
			pstm.setObject(index, value);
		} else if (value instanceof Integer) {
			pstm.setInt(index, (Integer) value);
		} else if (value instanceof Long) {
			pstm.setLong(index, (Long) value);
		} else if (value instanceof Float) {
			pstm.setFloat(index, (Float) value);
		} else if (value instanceof Double) {
			pstm.setDouble(index, (Double) value);
		} else if (value instanceof String) {
			pstm.setString(index, (String) value);
		} else if (value instanceof BigDecimal) {
			pstm.setBigDecimal(index, (BigDecimal) value);
		} else if (value instanceof Boolean) {
			pstm.setBoolean(index, (Boolean) value);
		} else if (value instanceof Timestamp) {
			pstm.setTimestamp(index, (Timestamp) value);
		} else if (value instanceof Time) {
			pstm.setTime(index, (Time) value);
		} else if (value instanceof Date) {
			Timestamp sd = new Timestamp(((Date) value).getTime());
			pstm.setTimestamp(index, sd);
		} else if (value instanceof BigDecimal) {
			pstm.setBigDecimal(index, (BigDecimal) value);
		}
	}
	
	
	
	/**
	 * prepare string for using in blank sql.
	 * @param o
	 * @return
	 */
	private String prepareString(Object o) {
		if (o == null) {
			return "";
		}
		String str = o.toString().trim();
		str = str.replace("\\", "\\\\");
		str = str.replace("'", "''");
		return str;
	}
	/**
	 * get the max primary key from database. used to set new object primary key.
	 * 得到当前数据库最大的主键的值，可以用来+1作为新插入数据的主键。
	 * @param objectClass the object to get max primary key of;
	 * @return the max primary key of current objects.
	 */
	public synchronized long getMaxPk(@SuppressWarnings("rawtypes") Class objectClass) {
		TableProvider tp = TableRegister.getTableProvider(objectClass);
		if (tp == null) {
			return -1;
		}
		String sql = "select nvl(max(" + tp.getPrimaryKey() + "), 0) from " + tp.getTableName();

		String spk = queryString(sql);
		if (spk != null && spk.length() > 0) {
			return (long) Long.parseLong(spk);
		};
		return -1;
	}
	
	
	

	/**
	 * feed values from ResultSet to Data object.
	 * @param object
	 * @param rs
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 */
	private void feedObject(Object object, ResultSet rs) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		Method[] setters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < setters.length; i++) {
			Method setter = setters[i];
			if (setter.getName().startsWith("set")) {
				Object[] parameters = new Object[1];
				String value = null;
				Object data = null;
				String columnName = SqlUtility.propertySetter2Column(setter.getName());
				try {
					value = rs.getString(columnName);
					data =  rs.getObject(columnName);
				} catch (Exception e) {
					//System.err.println(e.getLocalizedMessage() + " : "  + object.getClass().getSimpleName() + " " + columnName);
					continue;
				}
				if (value == null) {
					continue;
				}
				if (setter.getParameterTypes()[0].isInstance(data)) {
					parameters[0] =  data;
				} else if (setter.getParameterTypes()[0].equals(String.class)) {
					parameters[0] =  value;
				} else if (setter.getParameterTypes()[0].equals(Long.class) || setter.getParameterTypes()[0].equals(long.class)){
					parameters[0] =  new Long(value);
				} else if (setter.getParameterTypes()[0].equals(Integer.class) || setter.getParameterTypes()[0].equals(int.class)){
					parameters[0] =  new Integer(value);
				} else if (setter.getParameterTypes()[0].equals(Boolean.class) || setter.getParameterTypes()[0].equals(boolean.class)){
					parameters[0] =  new Boolean(value);
				} else if (setter.getParameterTypes()[0].equals(Float.class) || setter.getParameterTypes()[0].equals(float.class)){
					parameters[0] =  new Float(value);
				} else if (setter.getParameterTypes()[0].equals(Double.class) || setter.getParameterTypes()[0].equals(double.class)){
					parameters[0] =  new Double(value);
				} else if (Date.class.isAssignableFrom(setter.getParameterTypes()[0])){
					try {
						if (data instanceof String) {
							if (value.length() >= "yyyy-MM-dd HH:mm:ss".length()) {
								parameters[0] =  new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(value);
							} else if (value.length() >= "yyyy-MM-dd HH:mm".length()) {
								parameters[0] =  new SimpleDateFormat("yyyy-MM-dd HH:mm").parse(value);
							} else {
								parameters[0] =  new SimpleDateFormat("yyyy-MM-dd").parse(value);
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				} 

				if (parameters[0] != null) {
					setter.invoke(object, parameters);
				}
			}
		}
	}
	
	/**
	 * feed values from ResultSet to Data object.
	 * @param rs
	 * @return ResultSet data as a map.
	 * @throws SQLException 
	 */
	private Map<String, Object> feedMap(ResultSet rs) throws SQLException  {
		ResultSetMetaData rsmd = rs.getMetaData();
		Map<String, Object> result = new HashMap<String, Object>();

		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			Object o = rs.getObject(i);
			String key = rsmd.getColumnName(i).toLowerCase();
			key = SqlUtility.column2Property(key);
			result.put(key, o);
		}
		
		return result;
	}
	
	/**
	 * custom query a SQL from database and cast the result to object for give Class 
	 * 根据给定的blank sql来查询数据库，并范围给定类型的对象实例，查询的内容会注入到对象实例的属性中。
	 * @param sql sql to query
	 * @param objectClass the result object class type
	 * @return query result, make of objects of the give class.
	 * @throws SQLException 
	 */
	public<T> List<T> query(String sql, Class<T> objectClass) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = getConnection();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			Method[] setters = objectClass.getDeclaredMethods();
			List<T>  results = new ArrayList<T>();
			while (rs.next()) {
				T object = objectClass.newInstance();
				for (int i = 0; i < setters.length; i++) {
					Method setter = setters[i];
					if (setter.getName().startsWith("set")) {
						Object[] parameters = new Object[1];
						String value = null;
						Object data = null;
						String columnName = SqlUtility.propertySetter2Column(setter.getName());
						try {
							value = rs.getString(columnName);
							data =  rs.getObject(columnName);
						} catch (Exception e) {
							//System.err.println(e.getLocalizedMessage() + " : "  + objectClass.getSimpleName() + " " + columnName);
							continue;
						}
						if (value == null) {
							continue;
						}
						if (setter.getParameterTypes()[0].isInstance(data)) {
							parameters[0] =  data;
						} else if (setter.getParameterTypes()[0].equals(String.class)) {
							parameters[0] =  value;
						} else if (setter.getParameterTypes()[0].equals(Long.class) || setter.getParameterTypes()[0].equals(long.class)){
							parameters[0] =  new Long(value);
						} else if (setter.getParameterTypes()[0].equals(Integer.class) || setter.getParameterTypes()[0].equals(int.class)){
							parameters[0] =  new Integer(value);
						} else if (setter.getParameterTypes()[0].equals(Boolean.class) || setter.getParameterTypes()[0].equals(boolean.class)){
							parameters[0] =  new Boolean(value);
						} else if (setter.getParameterTypes()[0].equals(Float.class) || setter.getParameterTypes()[0].equals(float.class)){
							parameters[0] =  new Float(value);
						} else if (setter.getParameterTypes()[0].equals(Double.class) || setter.getParameterTypes()[0].equals(double.class)){
							parameters[0] =  new Double(value);
						} else if (setter.getParameterTypes()[0].equals(Date.class)){
							try {
								parameters[0] =  new SimpleDateFormat("yyyy-MM-dd").parse(value);
							} catch (Exception e) {
								e.printStackTrace();
							}
						} 

						if (parameters[0] != null) {
							setter.invoke(object, parameters);
						}
					}
				}
				results.add(object);
			}
			rs.close();
			pstm.close();
			return results;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			
			try {
				pstm.close();
			} catch (Exception e1) {}
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	/**
	 * custom query a SQL from database and cast the result to object for give Class 
	 * 根据给定的blank sql来查询数据库，并范围给定类型的对象实例，查询的内容会注入到对象实例的属性中。然后通过Visitor模式处理数据。
	 * 该模式下取一条数据，处理一条数据。不会一次吧所有数据加载内存。
	 * @param sql sql to query
	 * @param objectClass the result object class type
	 * @param ObjectVisitor to visit the record
	 * @return query result, make of objects of the give class.
	 * @throws SQLException 
	 */
	public<T> void query(String sql, Class<T> objectClass, ObjectVisitor oVisitor) {
		this.query(sql, objectClass, null, oVisitor);
	}
	/**
	 * custom query a SQL from database and cast the result to object for give Class 
	 * 根据给定的blank sql来查询数据库，并范围给定类型的对象实例，查询的内容会注入到对象实例的属性中。然后通过Visitor模式处理数据。
	 * 该模式下取一条数据，处理一条数据。不会一次吧所有数据加载内存。
	 * @param sql sql to query
	 * @param objectClass the result object class type
	 * @param valueMap， values for ？ in sql string.
	 * @param ObjectVisitor to visit the record
	 * @return query result, make of objects of the give class.
	 * @throws SQLException 
	 */
	public<T> void query(String sql, Class<T> objectClass, Map<Integer, Object> valueMap, ObjectVisitor oVisitor) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			//Visitor模式使用独享的一个连接。
			conn = dataSource.getConnection();
			if (this.monitor != null && conn != null) {
				this.monitor.addRequestConnection();
			}
			pstm = conn.prepareStatement(sql);
			if (valueMap != null) {
				for (int i : valueMap.keySet()) {
					this.setParameter(pstm, i, valueMap.get(i));
				}
			}
			rs = pstm.executeQuery();
			
			//call prepare
			oVisitor.prepare();
			while (rs.next()) {
				
				T object = objectClass.newInstance();				
				this.feedObject(object, rs);
				
				//call next
				oVisitor.next(object);
			}
			rs.close();
			pstm.close();
			conn.close();

			if (this.monitor != null) {
				this.monitor.addReturnConnection();
			}
			
			
			//call finish
			oVisitor.finish();
			return;
			
		} catch (Exception e) {
			
			try {
				rs.close();
			} catch (Exception e1) {}
			
			try {
				pstm.close();
			} catch (Exception e1) {}

			try {
				conn.close();
				if (this.monitor != null) {
					this.monitor.addReturnConnection();
				}
				
			} catch (Exception e1) {}
			
			e.printStackTrace();
			
			throw new RuntimeException(e);
		}
	}

	/**
	 * custom query a SQL from database and cast the result to map 
	 * 根据给定的blank sql来查询数据库，并Map对象，查询的内容会注入到对象实例的属性中。然后通过Visitor模式处理数据。
	 * 该模式下取一条数据，处理一条数据。不会一次吧所有数据加载内存。
	 * @param sql sql to query
	 * @param MapVisitor to visit the record
	 * @return query result, make of objects of the give class.
	 * @throws SQLException 
	 */
	public<T> void query(String sql, MapVisitor mVisitor) {
		this.query(sql, (Map<Integer, Object>) null, mVisitor);
	}
	
	/**
	 * custom query a SQL from database and cast the result to map 
	 * 根据给定的blank sql来查询数据库，并范围给定类型的对象实例，查询的内容会注入到对象实例的属性中。然后通过Visitor模式处理数据。
	 * 该模式下取一条数据，处理一条数据。不会一次吧所有数据加载内存。
	 * @param sql sql to query
	 * @param valueMap， values for ？ in sql string.
	 * @param MapVisitor to visit the record
	 * @return query result, make of objects of the give class.
	 * @throws SQLException 
	 */
	public<T> void query(String sql, Map<Integer, Object> valueMap, MapVisitor mVisitor) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			//Visitor模式使用独享的一个连接。
			conn = dataSource.getConnection();
			if (this.monitor != null && conn != null) {
				this.monitor.addRequestConnection();
			}
			
			pstm = conn.prepareStatement(sql);
			if (valueMap != null) {
				for (int i : valueMap.keySet()) {
					this.setParameter(pstm, i, valueMap.get(i));
				}
			}
			rs = pstm.executeQuery();
			
			//call prepare
			mVisitor.prepare();
			while (rs.next()) {
								
				//feed map
				Map<String, Object> map = this.feedMap(rs);
				
				//call visitor
				mVisitor.next(map);
			}
			rs.close();
			pstm.close();
			conn.close();

			if (this.monitor != null) {
				this.monitor.addReturnConnection();
			}
			
			//call finish
			mVisitor.finish();
			return;
			
		} catch (Exception e) {
			
			try {
				rs.close();
			} catch (Exception e1) {}
			
			try {
				pstm.close();
			} catch (Exception e1) {}

			try {
				conn.close();
				if (this.monitor != null) {
					this.monitor.addReturnConnection();
				}
				
			} catch (Exception e1) {}
			
			e.printStackTrace();
			
			throw new RuntimeException(e);
		}
	}
	/**
	 * query the custom SQL and return the first object of result
	 * 根据给定的blank sql来查询数据库并返回第一条结果，并范围给定类型的对象实例，查询的内容会注入到对象实例的属性中。
	 * @param sql String to query
	 * @param objectClass Class the result class type.
	 * @return first object of the search result.
	 * @throws SQLException 
	 */
	public<T> T queryFirst(String sql, Class<T> objectClass) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		try {
			
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			Method[] setters = objectClass.getDeclaredMethods();
			T object = null;
			if (rs.next()) {
				object = objectClass.newInstance();
				for (int i = 0; i < setters.length; i++) {
					Method setter = setters[i];
					if (setter.getName().startsWith("set")) {
						Object[] parameters = new Object[1];
						String value = null;
						Object data = null;
						String columnName = SqlUtility.propertySetter2Column(setter.getName());
						try {
							value = rs.getString(columnName);
							data =  rs.getObject(columnName);
						} catch (Exception e) {
							//System.err.println(e.getLocalizedMessage() + " : "  + objectClass.getSimpleName() + " " + columnName);
							continue;
						}
						if (value == null) {
							continue;
						}
						if (setter.getParameterTypes()[0].isInstance(data)) {
							parameters[0] =  data;
						} else if (setter.getParameterTypes()[0].equals(String.class)) {
							parameters[0] =  value;
						} else if (setter.getParameterTypes()[0].equals(Long.class) || setter.getParameterTypes()[0].equals(long.class)){
							parameters[0] =  new Long(value);
						} else if (setter.getParameterTypes()[0].equals(Integer.class) || setter.getParameterTypes()[0].equals(int.class)){
							parameters[0] =  new Integer(value);
						}else if (setter.getParameterTypes()[0].equals(Boolean.class) || setter.getParameterTypes()[0].equals(boolean.class)){
							parameters[0] =  new Boolean(value);
						} else if (setter.getParameterTypes()[0].equals(Float.class) || setter.getParameterTypes()[0].equals(float.class)){
							parameters[0] =  new Float(value);
						} else if (setter.getParameterTypes()[0].equals(Double.class) || setter.getParameterTypes()[0].equals(double.class)){
							parameters[0] =  new Double(value);
						} else if (setter.getParameterTypes()[0].equals(Date.class)){
							try {
								parameters[0] =  new SimpleDateFormat("yyyy-MM-dd").parse(value);
							} catch (Exception e) {
								e.printStackTrace();
							}
						} 
						if (parameters[0] != null) {
							setter.invoke(object, parameters);
						}
					}
				}
			}
			rs.close();
			pstm.close();
			return object;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			
			
			try {
				System.err.println(this.hashCode() + " : " + inTransaction + "; " + conn.hashCode() + " : " + conn.isClosed() + "; " + getConnection().hashCode() + " : " + getConnection().isClosed() + "(" + getConnection().equals(conn) + ")");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}			
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return null;
	}

	/**
	 * custom query a prepared SQL from database with params and cast the result to object for give Class 
	 * 根据给定的预编译的 sql来查询数据库，params参数来设置？的值。并返回给定类型的对象实例，查询的内容会注入到对象实例的属性中。
	 * @param sql prepared sql to query.要预编译的sql
	 * @param params 预编译sql要设置的？的值。
	 * @param objectClass the result object class type
	 * @return query result, make of objects of the give class.
	 */
	public<T> List<T> query(String sql, Object[] params, Class<T> objectClass) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			
			//set parameters
			for (int i = 1; i <= params.length; i++) {
				Object o = params[i-1];
				pstm.setObject(i, o);
			}
			
			//exec sql
			rs = pstm.executeQuery();
			Method[] setters = objectClass.getDeclaredMethods();
			List<T>  results = new ArrayList<T>();
			while (rs.next()) {
				T object = objectClass.newInstance();
				for (int i = 0; i < setters.length; i++) {
					Method setter = setters[i];
					if (setter.getName().startsWith("set")) {
						Object[] parameters = new Object[1];
						String value = null;
						Object data = null;
						String columnName = SqlUtility.propertySetter2Column(setter.getName());
						try {
							value = rs.getString(columnName);
							data =  rs.getObject(columnName);
						} catch (Exception e) {
							//System.err.println(e.getLocalizedMessage() + " : "  + objectClass.getSimpleName() + " " + columnName);
							continue;
						}
						if (value == null) {
							continue;
						}
						if (setter.getParameterTypes()[0].isInstance(data)) {
							parameters[0] =  data;
						} else if (setter.getParameterTypes()[0].equals(String.class)) {
							parameters[0] =  value;
						} else if (setter.getParameterTypes()[0].equals(Long.class) || setter.getParameterTypes()[0].equals(long.class)){
							parameters[0] =  new Long(value);
						} else if (setter.getParameterTypes()[0].equals(Integer.class) || setter.getParameterTypes()[0].equals(int.class)){
							parameters[0] =  new Integer(value);
						} else if (setter.getParameterTypes()[0].equals(Boolean.class) || setter.getParameterTypes()[0].equals(boolean.class)){
							parameters[0] =  new Boolean(value);
						} else if (setter.getParameterTypes()[0].equals(Float.class) || setter.getParameterTypes()[0].equals(float.class)){
							parameters[0] =  new Float(value);
						} else if (setter.getParameterTypes()[0].equals(Double.class) || setter.getParameterTypes()[0].equals(double.class)){
							parameters[0] =  new Double(value);
						} else if (setter.getParameterTypes()[0].equals(Date.class)){
							try {
								parameters[0] =  new SimpleDateFormat("yyyy-MM-dd").parse(value);
							} catch (Exception e) {
								e.printStackTrace();
							}
						} 

						if (parameters[0] != null) {
							setter.invoke(object, parameters);
						}
					}
				}
				results.add(object);
			}
			rs.close();
			pstm.close();
			return results;
			
		} catch (Exception e) {
			
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			
			
			System.err.println(sql);
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	
	/**
	 * custom execute a write SQL.
	 * 执行写入操作的blank sql语句。
	 * @param sql The SQL string to execute.
	 * @return operator Count of rows affected.
	 * @throws SQLException 
	 */
	public int write(String sql) throws SQLException {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		int i = 0;
		try {
			pstm = getConnection().prepareStatement(sql);
			i = pstm.executeUpdate();
			pstm.close();
		} catch (Exception e) {
			try {
				pstm.close();
			} catch (Exception e1) {}
			
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return i;
	}
	
	/**
	 * custom execute a series of write SQLs in one transaction. Roll back if any SQL failed.
	 * 在一个事物中执行一组写入操作的blank sql语句
	 * @param sqls The SQL string list to execute.
	 * @return operator row count.
	 * @throws SQLException 
	 */
	public boolean transactionWrite(List<String> sqls) throws SQLException {
		Statement stm = null;
		Connection conn = dataSource.getConnection();
		if (monitor != null) {
			monitor.addRequestConnection();
		}
		try {
			conn.setAutoCommit(false);
			if (monitor != null) {
				monitor.addStartedTrans(this.tracetool.locate(this));
			}
			stm = getConnection().createStatement();
			
			for (String sql :sqls) {
				if (isPrintSql()) {
					System.out.println(sql);
				}
				stm.executeUpdate(sql);
			}
			conn.commit();
			conn.setAutoCommit(true);
			stm.close();

			if (monitor != null) {
				monitor.addFinishedTrans(this.tracetool.locate(this));
			}
		} catch (SQLException e) {
			try {
				if (stm != null) {
					stm.close();
				}
				conn.rollback();
				conn.setAutoCommit(true);
				return false;
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			System.err.println(sqls);
			e.printStackTrace();
		} finally {
			conn.close();
			if (monitor != null) {
				monitor.addReturnConnection();
			}
			conn = null;
		}
		return true;
	}
	/**
	 * query a single string from database.
	 * 按照给定的blank sql语句查询数据库并返回第一条结果的第一个字段的值的String。
	 * @param sql string to query
	 * @return String, the first column of first object of the search result.
	 */
	public String queryString(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String value = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) {
				value = rs.getString(1);
			}
			rs.close();
			pstm.close();
			return value;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return value;
	}
	/**
	 * query a single string from database.
	 * 按照给定的blank sql语句查询数据库并返回第一条结果的第一个字段的值的对象。
	 * @param sql string to query
	 * @return String, the first column of first object of the search result.
	 */
	public Object queryValue(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Object value = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) {
				value = rs.getObject(1);
				if (value.getClass().getName().equalsIgnoreCase("oracle.sql.TIMESTAMP")) {
					value = rs.getTimestamp(1); //rs.getDate(i) + " " + rs.getTime(i);
				}
			}
			rs.close();
			pstm.close();
			return value;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return value;
	}
	
	/**
	 * query a single string from database.
	 * 按照给定的blank sql语句查询数据库并返回每一条结果记录的第一个字段的值的String。
	 * @param sql string to query
	 * @return the first column of objects of the search result.
	 */
	public List<String> queryStrings(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<String> values = new ArrayList<String>();
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				String value = rs.getString(1);
				values.add(value);
			}
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return values;
	}

	/**
	 * query the custom SQL and return the first object of results as a map
	 * 按照给定的blank sql语句查询数据库并返回第一条结果记录，以map的形式。
	 * @param sql String to query
	 * @param result the result map.
	 */
	public void queryFirst2Map(String sql, Map<String, Object> result) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		if (result == null) {
			result = new HashMap<String, Object> ();
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object o = rs.getObject(i);
					if (o != null && o.getClass().getName().toUpperCase().contains("TIMESTAMP")) {
						o = rs.getTimestamp(i).toString();
					}
					String key = rsmd.getColumnName(i).toLowerCase();
					key = SqlUtility.column2Property(key);
					result.put(key, o);
				}
			}
			rs.close();
			pstm.close();
			//return result;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
		//return null;
	}
	
	/**
	 * query the custom SQL and return the object of results as maps
	 * 按照给定的blank sql语句查询数据库并返回每条记录的map格式结果的结果集。
	 * @param sql String to query
	 * @return map的list，每一条结果记录作为一个map，
	 */
	public List<Map<String, Object>> query2Map(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> result = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object o = rs.getObject(i);
					if (o != null && o.getClass().getName().toUpperCase().contains("TIMESTAMP")) {
						o = rs.getTimestamp(i).toString();
					}
					String key = rsmd.getColumnName(i).toLowerCase();
					key = SqlUtility.column2Property(key);
					result.put(key, o);
				}
				results.add(result);
			}
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			logger.debug(sql);
			e.printStackTrace();
			
		}
		return results;
	}
	
	/**
	 * query the custom SQL and return the object of results as maps
	 * 按照给定的预编译 sql语句，以及给定的？的值，查询数据库并返回每条记录的map格式结果的结果集。
	 * @param sql String to query
	 */
	public List<Map<String, Object>> query2Map(String sql, Object[] params) {
		if (isPrintSql()) {
			System.out.println(sql);
			Describer.describe(params);
		}
		
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			
			//set parameters
			for (int i = 1; i <= params.length; i++) {
				Object o = params[i-1];
				pstm.setObject(i, o);
			}

			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> result = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object o = rs.getObject(i);
					if (o != null && o.getClass().getName().toUpperCase().contains("TIMESTAMP")) {
						o = rs.getTimestamp(i).toString();
					}
					String key = rsmd.getColumnName(i).toLowerCase();
					key = SqlUtility.column2Property(key);
					result.put(key, o);
				}
				results.add(result);
			}
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return results;
	}
	
	/**
	 * query the custom SQL and return the object of results as maps
	 * 按照给定的预编译 sql语句，以及给定的？的值，查询数据库并返回每条记录的map格式结果的结果集。
	 * @param sql String to query
	 */
	public void query2Json(String sql, Object[] params, PrintWriter pw) {
		if (isPrintSql()) {
			System.out.println(sql);
			Describer.describe(params);
		}
		SimpleDateFormat sf = new SimpleDateFormat(this.dateFormatStrJava);
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			
			//set parameters
			for (int i = 1; i <= params.length; i++) {
				Object o = params[i-1];
				pstm.setObject(i, o);
			}

			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			pw.print("[");
			boolean firstRecord = true;
			while (rs.next()) {
				if (firstRecord) {
					firstRecord = false;
				} else {
					pw.print(",");
				} 
				pw.print("{");
				boolean firstColumn = true;
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					if (firstColumn) {
						firstColumn = false;
					} else {
						pw.print(",");
					} 
					Object o = rs.getObject(i);
					if (o != null && o.getClass().getName().toUpperCase().contains("TIMESTAMP")) {
						o = rs.getTimestamp(i).toString();
					}
					String key = rsmd.getColumnName(i).toLowerCase();
					key = SqlUtility.column2Property(key);
					pw.print("\"" + key + "\":");
					if (o == null) {
						pw.print("null");
					} else if (o instanceof Number){
						pw.print(o + "");
					} else if (o instanceof Date){
						pw.print("\"" + sf.format((Date) o) + "\"");
					} else {
						pw.print("\"" + o + "\"");
					}
				}
				pw.print("}");
			}
			pw.print("]");
			pw.flush();
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
	}
	
	

	public String query2table(String sql) {
		return this.query2table(sql, true);
	}
	/**
	 * query the custom SQL and return the HTML table string of results
	 * 按照给定的blank sql语句查询数据库并返回table的HTML代码，包含thead标签和tbody标签，不包括table标签。
	 * @param sql String to query
	 * @return map的list，每一条结果记录作为一个map，2566.47
	 */
	public String query2table(String sql, boolean hasHead) {
		if (isPrintSql()) {
			System.out.println(sql);
		}

		StringBuffer sb = new StringBuffer();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			
			if (hasHead) {
				sb.append("<thead>\n\t<tr>\n");
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					sb.append("\t\t<th>").append(rsmd.getColumnName(i)).append("</th>\n");
				}
				sb.append("\t</tr>\n</thead>\n");
			}
			
			sb.append("<tbody>\n");
			while (rs.next()) {
				sb.append("\t<tr>\n");
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object o = rs.getObject(i);
					if (o != null && o.getClass().getName().toUpperCase().contains("TIMESTAMP")) {
						o = rs.getTimestamp(i).toString();
					}
					sb.append("\t\t<td>").append(o).append("</td>\n");
				}
				sb.append("\t</tr>\n");
			}
			sb.append("</tbody>\n");
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			
			try {
				rs.close();
			} catch (Exception e1) {}
			
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		
		return sb.toString();
	}
	
	/**
	 * query custom SQL and use a visitor pattern to take care the result.
	 * 按照给定的blank sql语句查询数据库并使用visitor模式来遍历结果集。visitor有参数提供。
	 * @param sql String to query
	 * @param visitor ResultVisitor implement.
	 */
	public void query(String sql, ResultVisitor visitor) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		if (visitor == null) {
			return;
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int ccount = rsmd.getColumnCount();
			String columns[] = new String[ccount];
			for (int i = 0; i < ccount; i ++) {
				columns[i] = rsmd.getColumnName(i + 1);
			}
			visitor.column(columns);
			
			while (rs.next()) {
				visitor.next();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object o = rs.getObject(i);
					if (o != null && o.getClass().getName().contains("TIMESTAMP")) {
						o = rs.getTimestamp(i);
					}
					visitor.data(rsmd.getColumnName(i), o);
				}
			}
			
			visitor.finish();
			rs.close();
			pstm.close();
			
			return;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return;
	}

	/**
	 * 要求入参在前面，出参在后面。
	 * @param name
	 * @param params
	 * @param returnType java.sql.Types.*;
	 * @return Object
	 */

	public Object[] call(String name, Object[] params, int[] returnTypes) {
		String sql = "{call " + name + "(%params%)}";
		
		String paramStr = "";
		
		//入参
		if (params != null && params.length > 0) {
			for (Object o : params) {
				paramStr += "?,";
			}
		}
		
		//回参
		if (returnTypes != null && returnTypes.length > 0) {
			for (int i : returnTypes) {
				paramStr += "?,";				
			}
		}
		
		//去逗号
		if (params.length > 0) {
			paramStr = paramStr.substring(0, paramStr.length() - 1);
		}
		
		sql = sql.replace("%params%", paramStr);
		
		if (isPrintSql()) {
			System.out.println(sql);
		}
		
		
		CallableStatement cstmt = null;
		Object[] result = null;
		
		try {
			cstmt = getConnection().prepareCall(sql);
			
			//in parameters//入参
			int paramPos = 0;
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					cstmt.setObject(i + 1, params[i]);
					paramPos = i + 1;
				}
			}
			
			//output parameters 回参
			if (returnTypes != null && returnTypes.length > 0) {
				for (int i = 0; i < returnTypes.length; i++) {
					cstmt.registerOutParameter(i + paramPos + 1, returnTypes[i]);		
				}
			}
			
			cstmt.executeUpdate(); 
			
			if (returnTypes != null && returnTypes.length > 0) {
				result = new Object[returnTypes.length];
				for (int i = 0; i < returnTypes.length; i++) {
					result[i] = cstmt.getObject(i + paramPos + 1);
				}
				
			}
			
			cstmt.close();
			
		} catch (Exception e) {
			
			try {
				cstmt.close();
			} catch (Exception e1) {}
			
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return result;
	}

	
	/**
	 * get columns(name, type and className) of a table
	 * 得到一个表的列名以及列对应的数据库数据类型和java数据类型
	 * @param tableName table name to get column.
	 * @return column informations array. each column information is a String array of[name, database type, java type];
	 */
	public String[][] getColumns(String tableName) {

		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement("select * from " + tableName + " where 1=2");
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			String[][] result = new String[rsmd.getColumnCount()][];
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				result[i - 1] = new String[3];
				result[i - 1][0] = rsmd.getColumnName(i);
				result[i - 1][1] = rsmd.getColumnTypeName(i);
				String type = rsmd.getColumnClassName(i);
				type = TableType2JavaType.map(type);
				result[i - 1][2] = type;
			}
			rs.close();
			pstm.close();
			return result;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			e.printStackTrace();
			
		}
		return null;
	}
	
	/**
	 * Get columns name and java type mapping of a table
	 * 得到一个表的所有列对应的java数据类型
	 * @param tableName table name to get column.
	 * @return column java type map.;
	 */
	public Map<String, String> getColumnJavaType(String tableName) {

		PreparedStatement pstm = null;
		ResultSet rs = null;
		Map<String, String> result = new HashMap<String, String>();
		try {
			pstm = getConnection().prepareStatement("select * from " + tableName + " where 1=2");
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				result.put(rsmd.getColumnName(i), rsmd.getColumnClassName(i));
			}
			rs.close();
			pstm.close();
			return result;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			e.printStackTrace();
			
		}
		return null;
	}


	
	/**
	 * 查询当前sda是否已经关闭。
	 * @return true：已经关闭。false：未关闭。
	 */
	public boolean isClosed() {
		return closed;
	}

	@Override
	protected void finalize() throws Throwable {
		try {
			
			
			if (!this.isClosed()) {
				this.close();
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		super.finalize();
	}

	
	
}
